Relational databases are utilized to archive, obtain access to and perform operations on data. Data in a relational database can be expressed in the form of a table having multiple entries. FIG. 1 depicts a conventional table 1 for a conventional relational database. The table 1 includes entries 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21 and 22. The entries 11 through 22 are arranged in rows and columns. The table 1 has four columns 2, 3, 4 and 5 having three entries each and three rows 6, 7 and 8 having four entries each. Thus, the table 1 is a four by three table. However, the table 1 could have any number of entries arranged in a different number of columns.
Often, a user desires to perform operations on data stored in the entries 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21 and 22 of the table 1. For example, a user might desire to determine the maximum or minimum value of a particular column, to perform a mathematical operation on the data in one or more entries 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21 and 22 or otherwise utilize the data stored in the table 1. In addition, operations on the relational database are typically implemented using structure query language (“SQL”). SQL has conventional built-in functions which can be used to perform these operations on the data in the table 1.
The conventional functions in SQL that are used by the relational database can typically be categorized as conventional scalar functions and conventional column functions. A conventional scalar function operates on a predetermined number of entries. For example, one conventional scalar function is the square root function (“SQRT”). This function always operates on a single entry and would, therefore, have a single argument as an input. The function SQRT (entry 11) would provide the square root of the value in entry 11 of the table 1. Another conventional scalar function might operate on more than one entry. However, the number of entries for which the conventional scalar function performs its operation is always predetermined by the function at the time the function is written.
A column function performs an operation on a set of data and returns a single result. As its name suggests, the conventional column function typically performs an operation on one column of data. For example, in the table 1, a conventional column function might perform an operation on all of the entries in one of the column 2, 3, 4 or 5. A conventional column function thus has one of the columns 2, 3, 4, or 5 as its argument. For example, a conventional minimum function (MIN) could be written as MIN (col. 2) and would return the minimum of column 2.
A conventional column function can also perform an operation on an indeterminate number of entries. In other words, the actual number of entries for which the conventional column performs an operation is not determined at the time the conventional column function is written. Instead, the number of entries for which conventional column function performs an operation depends upon the number of entries in the column that is used as an argument for the conventional column function. For example, the table 1 has three rows 6, 7 and 8. A conventional column function which operates on, for example, column 2 would operate on the three entries that are the portions of the three rows 6, 7 and 8 corresponding to column 2. If the same conventional column function is used with a different table having a different number of rows, then the number of entries operated on would be different. Consequently, the conventional column function utilizes an indeterminate number of entries. Note that although the actual number of entries is not predetermined, in a particular implementation, there may be some upper limit to the number of entries allowed in a column, for example due to hardware or other limitations. Examples of a conventional column function include minimum, maximum, sum, and average functions which return the minimum value, maximum value, sum of all values, and average of all values, respectively, for the entries in a column. For example, the maximum of the column 2 would provide the maximum of the values stored in entries 11, 15, and 19 of the column 2 in table 1.
Processing of a conventional column function typically includes three phases: initialization, evaluation and finalization. In the initialization phase, the column function performs the procedures necessary for commencing the column function. For example, counters could be cleared and flags reset. In the evaluation phase, the column function actually performs the processes required for data in individual entries of the table 1. The finalization phase carries out any additional processes required to provide the output of the column function.
FIG. 2 depicts a conventional method 50 for performing a conventional column function. The conventional method 50 will be described in the context of the table 1 depicted in FIG. 1. For ease of explanation, it is assumed that the conventional column function is to be performed starting with column 3 of the table 1. Referring to FIGS. 1 and 2, a row 6 is fetched, via step 52. If the entry is the first entry of the column, then the initialization phase is performed, via step 54. Because the row 6 includes the first entry 12 of the column 3, initialization is performed in step 54. The evaluation phase would then be performed, via step 56. Steps 52-56 are then repeated for the remaining rows in the table 1, via step 58. Thus, the next row 7 would be fetched and the data in the entry 16 corresponding to the column 3 evaluated. The row 8 would then be fetched and the data in the entry 20 corresponding to the column 3 evaluated. Once evaluation is performed for row 8, the last row in the table 1, the finalization phase is carried out, via step 60. Thus, the column function would perform operations for each entry in the column 3 that is an argument for the column function.
Although the conventional method 50 allows the conventional column function to perform operations on an indeterminate number of entries, one of ordinary skill in the art will readily recognize that the conventional column function can only operate based on one of the columns 2, 3, 4 and 5. One of ordinary skill in the art will also readily recognize that a user may also desire to perform similar operations on the rows 6, 7 and 8. However, SQL defines a data set, on which the conventional column function operates, as including one or more of the columns 2, 3, 4 and 5. SQL does not have a mechanism for defining a data set based on the rows 6, 7 or 8 rather than the columns 2, 3, 4 and 5. Thus, the conventional column function cannot perform its operations based on rows 6, 7 and 8 instead of columns 2, 3, 4 and 5.
In order to perform column functions for rows of data, a developer can write conventional row functions that are analogous to the conventional column functions and which are specially designed to perform operations on rows 6, 7 or 8 of data. However, to do so would require a significant expenditure of time and resources. It would also be desirable to avoid this expenditure of time and resources.
Accordingly, what is needed is a system and method for performing operations for column functions based on rows instead of columns. The present invention addresses such a need.